﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class EntityManager:ConnectionManager
    {
        public SqlException AddNewEntity(EntityUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO M_ENTITIES (EntityCode, EntityDescription, EntityStatus) VALUES (@EntityCode, @EntityDescription, @EntityStatus)";

                oCommand.Parameters.Add(new SqlParameter("@EntityCode", oUnit.EntityCode));
                oCommand.Parameters.Add(new SqlParameter("@EntityDescription", oUnit.EntityDescription));
                oCommand.Parameters.Add(new SqlParameter("@EntityStatus", oUnit.EntityStatus));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateEntity(DataTable dt)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                foreach (DataRow oRow in dt.Rows)
                {
                    if (oRow.RowState == DataRowState.Added)
                    {
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = "INSERT INTO M_ENTITIES (EntityCode, EntityDescription, EntityStatus) VALUES (@EntityCode, @EntityDescription, @EntityStatus)";

                        oCommand.Parameters.Add(new SqlParameter("@EntityCode", oRow["EntityCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@EntityDescription", oRow["EntityDescription"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@EntityStatus", oRow["EntityStatus"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }

                    if (oRow.RowState == DataRowState.Modified)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = "UPDATE M_ENTITIES SET EntityCode=@EntityCode, EntityDescription=@EntityDescription, EntityStatus=@EntityStatus WHERE EntityNo=@EntityNo";

                        oCommand.Parameters.Add(new SqlParameter("@EntityNo", oRow["EntityNo"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@EntityCode", oRow["EntityCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@EntityDescription", oRow["EntityDescription"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@EntityStatus", oRow["EntityStatus"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }
                }
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public DataTable GetEntities()
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_ENTITIES";

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
    }
}
